﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class frm_Add_MaterialOrderDetailByComp : Form
    {
        public string order_number;

        private INISettings mySetting;
        private SqlConnection myConnection;

        private DataTable Detail_Table;

        public frm_Add_MaterialOrderDetailByComp()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.order_number = null;

            this.Detail_Table = new DataTable();
            this.Detail_Table.Columns.Add("OrderNo", typeof(string));
            this.Detail_Table.Columns.Add("MaterialID", typeof(string));
            this.Detail_Table.Columns.Add("Quantity", typeof(float));
            this.Detail_Table.Columns.Add("MOQ", typeof(float));
            this.Detail_Table.Columns.Add("Marume", typeof(float));
            this.Detail_Table.Columns.Add("OrderQuantity", typeof(float));
            this.Detail_Table.Columns.Add("RoHS", typeof(bool));
            this.Detail_Table.Columns.Add("MakerID", typeof(string));
            this.Detail_Table.Columns.Add("MakerName", typeof(string));
            this.Detail_Table.Columns.Add("PackingNote", typeof(string));
            this.Detail_Table.Columns.Add("Price", typeof(float));
            this.Detail_Table.Columns.Add("Currency", typeof(string));
            this.Detail_Table.Columns.Add("Deadline", typeof(DateTime));

            LoadComp();           
        }

        ///////////////////////////////////////////////
        /// Calculate MOQ and Marume
        //////////////////////////////////////////////
        private double CalculateMarumeMOQ(double qty, double moq, double marume)
        {
            double ret = 0;
            if (marume != 0)
            {
                ret = Math.Ceiling(qty / marume) * marume;
                if (moq > ret)
                {
                    ret = moq;
                }
            }
            return ret;
        }
                
        //////////////////////////////////////////////
        /// Calcaulate Price with PartNo and Quantity
        //////////////////////////////////////////////
        private string[] calculatePrice(string part, float qty)
        {
            string[] ret = new string[2];
            ret[0] = "0";
            ret[1] = "USD";

            float max_qty = 0;
            bool pass = true;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT Quantity"
                    + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "' AND Quantity < 99999"
                    + " ORDER BY Quantity DESC";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    max_qty = float.Parse(dataReader["Quantity"].ToString());
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                pass = false;
            }

            if (pass)
            {
                if (qty < max_qty)
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT Price, CurrencyID"
                            + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                            + " AND Quantity<=" + qty
                            + " ORDER BY Price";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            ret[1] = dataReader["CurrencyID"].ToString();
                            ret[0] = dataReader["Price"].ToString();
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get price for this PartNo.");
                    }
                }
                else
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT Price, CurrencyID"
                            + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                            + " ORDER BY Price";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            ret[1] = dataReader["CurrencyID"].ToString();
                            ret[0] = dataReader["Price"].ToString();
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get price for this PartNo.");
                    }
                }
            }
            else
            {
                MessageBox.Show("Cannot get price for this PartNo.");
            }

            return ret;
        }
        
        //////////////////////////////////////////////
        /// Load Material List
        //////////////////////////////////////////////
        private void LoadComp()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT DISTINCT RobotNo FROM [231_Kosei]"                    
                    + " ORDER BY RobotNo";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["RobotNo"].ToString().Trim();
                    item.Value = dataReader["RobotNo"].ToString().Trim();

                    this.robotNo.Properties.Items.Add(item);
                }                
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }

                MessageBox.Show("Cannot get Material information");
            }
        }

        /////////////////////////////////////
        /// Set Order No
        ////////////////////////////////////
        public void setOrderNo(string value)
        {
            order_number = value;
            this.orderNo.Text = value;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {            
            if (this.Detail_Table.Rows.Count > 0)
            {                
                try
                {
                    myConnection.Open();
                    foreach (DataRow add_row in Detail_Table.Rows)
                    {
                        string sql = "INSERT INTO [444_MaterialOrderDetail] (OrderNo, MaterialID, Quantity, DeadLine)"
                            + " VALUES ('" + add_row["OrderNo"].ToString() + "', '" + add_row["MaterialID"].ToString() + "',"
                            + " " + add_row["OrderQuantity"].ToString() + ", @deadline)";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.Parameters.Add("@deadline", SqlDbType.DateTime).Value = DateTime.Parse(add_row["Deadline"].ToString());
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                    }
                    myConnection.Close();
                    MessageBox.Show("Material Orther Detail added success.");
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot add Material Order Detail. Please try again.");
                }
            }
        }

        private void buttonPreview_Click(object sender, EventArgs e)
        {
            this.Detail_Table.Rows.Clear();
            string err = null;
            string comp = null;
            float qty = 0;
            DateTime deadline = new DateTime();

            if (this.robotNo.SelectedIndex < 0)
            {
                err = "Please select CompID.";
            }
            else
            {
                ComboboxItem item = (ComboboxItem)this.robotNo.SelectedItem;
                comp = item.Value.ToString();
            }

            if (string.IsNullOrEmpty(err))
            {
                if (string.IsNullOrEmpty(this.deadLine.EditValue.ToString()))
                {
                    err = "Please select Dealine Date";
                }
                else
                {
                    deadline = DateTime.Parse(this.deadLine.EditValue.ToString());
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text))
                {
                    err = "Please enter Quantity";
                }
                else
                {
                    if (float.TryParse(this.quantityValue.Text, out qty))
                    {
                        if (qty <= 0)
                        {
                            err = "Quantity must be greater than 0";
                        }
                    }
                    else
                    {
                        err = "Invalid quantity.";
                    }
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT t1.[PartNo], t1.[Quantity], t1.[Group],"
                        + " CASE WHEN (t2.ROHS IS NULL) OR (t2.ROHS = '') THEN 0 ELSE t2.ROHS END AS ROHS, t3.[EnglishName],"
                        + " CASE WHEN (t2.[MinQuantity] IS NULL) OR (t2.[MinQuantity] = 0) THEN 1 ELSE t2.[MinQuantity] END AS [MinQuantity],"
                        + " CASE WHEN (t2.[Marume] IS NULL) OR (t2.[Marume] = 0) THEN 1 ELSE t2.[Marume] END AS Marume, t2.MakerID,"
                        + " CASE WHEN (t2.Remark IS NULL) OR (t2.Remark = '') THEN t2.PackingNote ELSE t2.Remark END AS Note"
                        + " FROM [231_Kosei] AS t1 LEFT OUTER JOIN"
                        + " [222_Part] AS t2 ON t1.PartNo=t2.PartNo LEFT JOIN"
                        + " [222_Maker] AS t3 ON t2.MakerID=t3.MakerID"
                        + " WHERE t1.RobotNo='" + comp + "'";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        double quantity = 0;
                        if (double.TryParse(dataReader["Quantity"].ToString().Trim(), out quantity))
                        {
                            quantity = quantity * qty;
                        }
                        double marume = 0;
                        double moq = 0;
                        double.TryParse(dataReader["Marume"].ToString().Trim(), out marume);
                        double.TryParse(dataReader["MinQuantity"].ToString().Trim(), out moq);
                        double order_qty = 0;

                        DataRow new_row = this.Detail_Table.NewRow();
                        new_row["OrderNo"] = this.order_number;
                        new_row["MaterialID"] = dataReader["PartNo"].ToString().Trim();
                        new_row["Quantity"] = quantity;
                        new_row["MOQ"] = moq;
                        new_row["Marume"] = marume;
                        order_qty = CalculateMarumeMOQ(quantity, moq, marume);
                        new_row["OrderQuantity"] = order_qty;
                        int rohs = 0;
                        Int32.TryParse(dataReader["ROHS"].ToString(), out rohs);
                        if (rohs > 0)
                        {
                            new_row["RoHS"] = true;
                        }
                        else
                        {
                            new_row["RoHS"] = false;
                        }
                        new_row["MakerID"] = dataReader["MakerID"].ToString().Trim();
                        new_row["MakerName"] = dataReader["EnglishName"].ToString().Trim();
                        new_row["PackingNote"] = dataReader["Note"].ToString().Trim();
                        new_row["Deadline"] = deadline.Date;
                        this.Detail_Table.Rows.Add(new_row);
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    err = ex.Message;
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                if (this.Detail_Table.Rows.Count > 0)
                {
                    foreach (DataRow detail_row in this.Detail_Table.Rows)
                    {
                        string[] quotation = calculatePrice(detail_row["MaterialID"].ToString(), float.Parse(detail_row["OrderQuantity"].ToString()));
                        detail_row.BeginEdit();
                        detail_row["Price"] = float.Parse(quotation[0]);
                        detail_row["Currency"] = quotation[1];
                        detail_row.EndEdit();
                    }

                    this.gridCompDetails.DataSource = this.Detail_Table;
                    CompDetails.BestFitColumns();
                }
            }
            else
            {
                MessageBox.Show(err);
            }
        }

        private void robotNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.Detail_Table.Rows.Clear();
        }
    }
}
